package com;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CarDB {
    //获取连接类
    private static Connection getConnection() {
        Connection connection = null;
        try {
            //获取驱动,这里的类名需要在数据库客户端（DBeaver为例）中,编辑连接->编辑驱动设置->类名
            Class.forName("com.mysql.cj.jdbc.Driver");

            //创建连接：默认串:数据库+localhost:端口号+数据库名,数据库用户名，数据库密码
            //?useUnicode=true&characterEncoding=utf8使用utf-8编码数据库可以存入中文
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/cardb?useUnicode=true&characterEncoding=utf8",
                    "root",
                    "root");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 插入账号
     *
     * @param user 用户数据
     * @return 结果0代表失败，1代表插入成功，2账号已存在
     */
    public int insertUser(User user) {
        String sql = "insert into user_tbl(image,account,password,name,gender,tel,manager) values(?,?,?,?,?,?,?)";
        //获取连接
        Connection connection = getConnection();
        int result = 0;
        try {
            //执行SQl语句的对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //为SQl中的参数赋值
            preparedStatement.setString(1, user.getImageName());
            preparedStatement.setString(2, user.getAccount());
            preparedStatement.setString(3, user.getPassword());
            preparedStatement.setString(4, user.getName());
            preparedStatement.setInt(5, user.getGender());
            preparedStatement.setString(6, user.getTel());
            preparedStatement.setInt(7, user.getManager());

            //增删改使用executeUpdate执行sql代码
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return result;
    }

    /**
     * 插入车辆信息
     *
     * @param car 车辆信息
     * @return 结果0代表失败，1代表插入成功
     */
    public int insertCar(Car car) {
        String sql = "INSERT INTO car_tbl(account,carNumber,carBand,image,freeTime,state,checkState) VALUES (?,?,?,?,?,?,?)";
        //获取连接
        Connection connection = getConnection();
        int result = 0;
        try {
            //执行SQl语句的对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //为SQl中的参数赋值
            preparedStatement.setString(1, car.getAccount());
            preparedStatement.setString(2, car.getCarNumber());
            preparedStatement.setString(3, car.getCarBand());
            preparedStatement.setString(4, car.getImage());
            preparedStatement.setString(5, car.getFreeTime());
            preparedStatement.setInt(6, car.getState());
            preparedStatement.setInt(7, car.getCheck());

            //增删改使用executeUpdate执行sql代码
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return result;
    }

    /**
     * 查询所有未审核通过的车辆信息
     *
     * @param check 审核情况
     * @return 数据
     */
    public List<Car> queryNoCheckCarList(int check) {
        List<Car> list = new ArrayList<>();
        //sql语句

        String sql = "select user_tbl.account,user_tbl.name,car_tbl.image,car_tbl.carNumber,car_tbl.carBand, car_tbl.freeTime,car_tbl.state from car_tbl,user_tbl where user_tbl.account=car_tbl.account and car_tbl.checkState=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //执行查询语句获取账号信息
            preparedStatement.setInt(1, check);
            //获取查询的数据
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new Car(resultSet.getString("account"),
                        resultSet.getString("name"),
                        resultSet.getString("carNumber"),
                        resultSet.getString("carBand"),
                        resultSet.getString("image"),
                        resultSet.getString("freeTime"),
                        resultSet.getInt("state"),
                        check)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return list;
    }


    /**
     * 通过账号查询用户被审核数据
     * 包括用户表的项目和车辆表的所有信息
     *
     * @param account 账号
     * @return 数据
     */

    public List<Car> queryCarByAccount(String account) {
        List<Car> list = new ArrayList<>();
        //sql语句-----通过外键account获取用户表中的用户姓名
        String sql = "select user_tbl.name,car_tbl.image,car_tbl.carNumber,car_tbl.carBand, car_tbl.freeTime,car_tbl.state,car_tbl.checkState from user_tbl,car_tbl where user_tbl.account=car_tbl.account and car_tbl.account=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //执行查询语句获取账号信息
            preparedStatement.setString(1, account);
            //获取查询的数据
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new Car(account,
                        resultSet.getString("name"),
                        resultSet.getString("carNumber"),
                        resultSet.getString("carBand"),
                        resultSet.getString("image"),
                        resultSet.getString("freeTime"),
                        resultSet.getInt("state"),
                        resultSet.getInt("checkState"))
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return list;
    }


    /**
     * 通过账号查询数据
     *
     * @param account 需要查询的账号
     * @return 返回查询到的数据，没有则返回null
     */
    public User queryDataByAccount(String account) {
        User user = null;
        //SQL语句
        String sql = "select * from user_tbl where account=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            //执行查询语句获取账号信息
            ResultSet resultSet = preparedStatement.executeQuery();
            //获取查询的数据
            if (resultSet.next()) {
                user = new User(resultSet.getString("image"),
                        resultSet.getString("account"),
                        resultSet.getString("password"),
                        resultSet.getString("name"),
                        resultSet.getInt("gender"),
                        resultSet.getString("tel"),
                        resultSet.getInt("manager")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return user;
    }

    /**
     * 插入车辆订单信息
     *
     * @param order 车辆预约订单信息
     * @return 结果0代表失败，1代表插入成功
     */
    public int insertOrder(Order order) {
        String sql = "INSERT INTO order_tbl(account,carNumber,startDate,startTime,finishDate,finishTime,state) VALUES (?,?,?,?,?,?,?)";
        //获取连接
        Connection connection = getConnection();
        int result = 0;
        try {
            //执行SQl语句的对象
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //为SQl中的参数赋值
            preparedStatement.setString(1, order.getAccount());
            preparedStatement.setString(2, order.getCarNumber());
            preparedStatement.setString(3, order.getStartDate());
            preparedStatement.setString(4, order.getStartTime());
            preparedStatement.setString(5, order.getFinishDate());
            preparedStatement.setString(6, order.getFinishTime());
            preparedStatement.setInt(7, order.getState());

            //增删改使用executeUpdate执行sql代码
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return result;
    }

    /**
     * 查找账号对应的电话号码
     *
     * @param account 账号
     * @return 对应的电话号码null则代表无号码
     */
    public String queryTelByAccount(String account) {
        String tel = null;
        //SQL语句
        String sql = "select tel from user_tbl where account=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            //执行查询语句获取账号信息
            ResultSet resultSet = preparedStatement.executeQuery();
            //获取查询的数据
            if (resultSet.next()) {
                tel = resultSet.getString("tel");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return tel;
    }


    /**
     * 查询车辆信息
     *
     * @return 返回预约中的车辆信息
     */
    public List<Car> homeQueryCarData() {
        List<Car> carList = new ArrayList<>();
        //查询通过审核同时有空闲时间的车辆信息
        String sql = "select user_tbl.name,car_tbl.account,car_tbl.carNumber,car_tbl.carBand,car_tbl.image,car_tbl.freeTime from user_tbl,car_tbl where car_tbl.account=user_tbl.account and state=0 and checkState=0";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                carList.add(new Car(resultSet.getString("account"),
                        resultSet.getString("name"),
                        resultSet.getString("carNumber"),
                        resultSet.getString("carBand"),
                        resultSet.getString("image"),
                        resultSet.getString("freeTime"),
                        0, 0
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return carList;
    }

    /**
     * 查询车辆预定订单信息
     *
     * @return 返回预约中的订单信息
     */
    public List<AppointOrder> queryAppointOrderData(String account) {
        List<AppointOrder> appointOrderList = new ArrayList<>();
        //查询通过审核同时有空闲时间的车辆信息
        String sql = "select user_tbl.name,user_tbl.tel,order_tbl.account,order_tbl.carNumber,car_tbl.carBand,car_tbl.image,car_tbl.freeTime,order_tbl.startDate,order_tbl.startDate,order_tbl.startTime,order_tbl.finishDate,order_tbl.finishTime,order_tbl.state from user_tbl,car_tbl,order_tbl where order_tbl.account=user_tbl.account and order_tbl.carNumber=car_tbl.carNumber and order_tbl.account=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                appointOrderList.add(new AppointOrder(
                        resultSet.getString("name"),
                        resultSet.getString("tel"),
                        resultSet.getString("carBand"),
                        resultSet.getString("image"),
                        resultSet.getString("freeTime"),
                        new Order(
                                resultSet.getString("account"),
                                resultSet.getString("carNumber"),
                                resultSet.getString("startDate"),
                                resultSet.getString("startTime"),
                                resultSet.getString("finishDate"),
                                resultSet.getString("finishTime"),
                                resultSet.getInt("state")
                        )));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return appointOrderList;
    }

    /**
     * 查询车辆预定订单信息通过状态查询不同的订单
     *
     * @return 返回预约中的订单信息
     */
    public List<AppointOrder> queryOrderByState(String account, int state) {
        List<AppointOrder> orderList = new ArrayList<>();
        //查询通过审核同时有空闲时间的车辆信息
        String sql = "select user_tbl.name,user_tbl.tel,order_tbl.account,order_tbl.carNumber,car_tbl.carBand,car_tbl.image,car_tbl.freeTime,order_tbl.startDate,order_tbl.startDate,order_tbl.startTime,order_tbl.finishDate,order_tbl.finishTime,order_tbl.state from user_tbl,car_tbl,order_tbl where order_tbl.account=user_tbl.account and order_tbl.carNumber=car_tbl.carNumber and order_tbl.account=? and order_tbl.state=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.setInt(2, state);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                orderList.add(new AppointOrder(
                        resultSet.getString("name"),
                        resultSet.getString("tel"),
                        resultSet.getString("carBand"),
                        resultSet.getString("image"),
                        resultSet.getString("freeTime"),
                        new Order(
                                resultSet.getString("account"),
                                resultSet.getString("carNumber"),
                                resultSet.getString("startDate"),
                                resultSet.getString("startTime"),
                                resultSet.getString("finishDate"),
                                resultSet.getString("finishTime"),
                                state
                        )));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return orderList;
    }

    //抽出方法：关闭连接
    private static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 检查账号是否存在
     *
     * @param account 账号
     * @return 是否存在0代表已存在，1代表不存在
     */
    public int checkAccountIfExist(String account) {
        //SQL语句
        String sql = "select * from user_tbl where account=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            //执行查询语句获取账号信息
            ResultSet resultSet = preparedStatement.executeQuery();
            //获取查询的数据
            if (resultSet.next()) {
                return 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return 1;
    }

    /**
     * 更新账号的密码
     *
     * @param account     账号
     * @param newPassword 新的密码
     * @return 更新的结果成功返回1，不成功返回0
     */
    public int updatePassword(String account, String newPassword) {
        int result = 0;
        //SQL语句
        String sql = "UPDATE user_tbl SET password=? WHERE account=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, newPassword);
            preparedStatement.setString(2, account);
            //执行查询语句获取账号信息
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return result;
    }

    /**
     * 检测账号是否匹配
     *
     * @param account  账号
     * @param password 密码
     * @return 返回结果0代表密码错误，1代表正确
     */
    public int checkAccountAndPassword(String account, String password) {
        //SQL语句
        String sql = "select password from user_tbl where account=?";
        //获取连接
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            //执行查询语句获取账号信息
            ResultSet resultSet = preparedStatement.executeQuery();
            //获取查询的数据
            if (resultSet.next()) {
                //获取账号中的密码
                String psw = resultSet.getString("password");
                //比较密码是否正确，正确返回
                if (password.equals(psw)) {
                    return 1;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return 0;
    }

    /**
     * 更新车辆的空闲时间
     *
     * @param carNumber   车牌
     * @param newFreeTime 新的空闲时间
     * @return 更新的结果成功返回1，不成功返回0
     */
    public int updateCarFreeTime(String carNumber, String newFreeTime) {
        int result = 0;
        //SQL语句跟新车辆的空闲时间
        String sql = "UPDATE car_tbl SET freeTime=? WHERE carNumber=?";

        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, newFreeTime);
            preparedStatement.setString(2, carNumber);
            //执行更新语句更新车辆空闲时间
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return result;
    }

    /**
     * 更新管理员审核状态
     *
     * @param carNumber
     * @return 是否成功
     */

    public static int updatacheckcar(String carNumber) {
        //sql语句
        int result = 0;
        String sql = "update car_tbl set checkState=0 where carNumber=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, carNumber);
            //执行sql
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }

        return result;
    }


    /**
     * 取消订单
     * 更新订单状态
     *
     * @param account   账号
     * @param carNumber 新的密码
     * @return 更新的结果成功返回1，不成功返回0
     */
    public int cancelAppointOrder(String account, String carNumber) {
        int result = 0;
        //SQL语句
        String sql = "UPDATE order_tbl SET state=0 WHERE account=? and carNumber=?";
        Connection connection = getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            preparedStatement.setString(2, carNumber);
            //执行查询语句获取账号信息
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
        return result;
    }
}
